package dialect

import (
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"strings"

	"gitee.com/dingiyan/goMyAdmin/service/interfaces"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// mysql 数据库方言，实现统一接口操作的对象
type MySQL struct {
	DB *gorm.DB
}

func NewMySQL() *MySQL {
	return &MySQL{}
}

// 连接一个数据源
func (d *MySQL) ConnectDatasources(config *interfaces.DatasourcesConfig) (*gorm.DB, error) {
	if d.DB != nil {
		return d.DB, nil
	}
	if config.Database == "" {
		config.Database = "mysql"
	}
	dsn := fmt.Sprintf("%v:%v@tcp(%v:%v)/%v?charset=utf8mb4&parseTime=True&loc=Local", config.User, config.Password, config.Host, config.Port, config.Database)
	log.Printf("dsn: %v\r\n", dsn)
	// 首先尝试从缓存中拿
	cacheObj, ok := pools[dsn]
	if ok {
		d.DB = cacheObj
		return cacheObj, nil
	}

	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		return nil, err
	}
	pools[dsn] = db

	d.DB = db
	return db, nil
}

// 创建一个数据库
func (d *MySQL) CreateDatabase(database string, options map[string]any) error {
	if d.DB == nil {
		return fmt.Errorf("db is nil")
	}
	sql := fmt.Sprintf("CREATE DATABASE IF NOT EXISTS %v DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;", database)
	return d.DB.Exec(sql).Error
}

// 删除一个数据库
func (d *MySQL) DropDatabase(database string) error {
	if d.DB == nil {
		return fmt.Errorf("db is nil")
	}
	sql := fmt.Sprintf("DROP DATABASE IF EXISTS %v;", database)
	return d.DB.Exec(sql).Error
}

// GetDatabases implements interfaces.DBManager.
func (d *MySQL) GetDatabases() ([]interfaces.DatabaseInfoObject, error) {
	sql := "SHOW DATABASES;"
	var list []interfaces.DatabaseInfoObject
	var origin []map[string]any
	err := d.DB.Raw(sql).Find(&origin).Error
	for _, v := range origin {
		name := v["Database"].(string)
		list = append(list, interfaces.DatabaseInfoObject{
			Name: name,
		})
	}
	return list, err
}

// 创建一个表
func (d *MySQL) CreateTable(table string, fields []interfaces.FieldOptions, args ...any) error {
	if d.DB == nil {
		return fmt.Errorf("db is nil")
	}

	sql := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %v (\r\n", table)

	for index, v := range fields {

		sql += fmt.Sprintf("%v %v", v.Name, v.Type)

		if !v.IsAutoIncrement {
			if v.IsNull {
				sql += " NULL"
			} else {
				sql += " NOT NULL"
			}
		}

		if v.IsAutoIncrement {
			sql += " AUTO_INCREMENT"
		}
		if v.IsPrimary {
			sql += " PRIMARY KEY"
		}
		if v.Default != "" {
			sql += fmt.Sprintf(" DEFAULT '%v'", v.Default)
		}

		if v.Comment != "" {
			sql += fmt.Sprintf(" COMMENT '%v'", v.Comment)
		}

		// 字段间隔符
		if index < len(fields)-1 {
			sql += ",\r\n"
		}

	}

	var engine = "InnoDB"
	var charset = "utf8mb4"
	var collate = "utf8mb4_general_ci"
	var comment = ""
	// 方言附加参数配置，mysql可设置引擎和字符集
	if len(args) > 0 && args[0] != nil {

		arg, ok := args[0].(map[string]any)
		if ok && arg != nil {
			// 断言需要第二个参数，否则断言失败时将会panic
			charset1, _ := arg["charset"].(string)
			collate1, _ := arg["collate"].(string)
			engine1, _ := arg["engine"].(string)
			comment1, _ := arg["comment"].(string)

			log.Printf("mysql dialect addon args: charset:%v collate:%v engine:%v comment:%v", charset1, collate1, engine1, comment1)

			if engine1 != "" {
				engine = engine1
			}
			if collate1 != "" {
				collate = collate1
			}
			if charset1 != "" {
				charset = charset1
			}
			if comment1 != "" {
				comment = comment1
			}

		} else {
			log.Println("mysql dialect args assert error")
		}

	}

	if comment != "" {
		sql += fmt.Sprintf("\r\n) ENGINE=%v DEFAULT CHARSET=%v COLLATE=%v COMMENT='%v';", engine, charset, collate, comment)
	} else {
		sql += fmt.Sprintf("\r\n) ENGINE=%v DEFAULT CHARSET=%v COLLATE=%v;", engine, charset, collate)
	}

	log.Println(sql)

	return d.DB.Exec(sql).Error
}

// 将切片转换为map
func ConvertToMap(fields []interfaces.FieldOptions) map[string]interfaces.FieldOptions {
	fieldMap := make(map[string]interfaces.FieldOptions)
	for _, field := range fields {
		fieldMap[field.Name] = field // 使用字段 Name 作为键，FieldOptions 作为值
	}
	return fieldMap
}

// 更新表
// 生成 ALTER TABLE 语句，支持字段更新、表名变更和表注释变更
func (d *MySQL) GenerateAlterTableSQL(databaseName, table, newTableName string, fields []interfaces.FieldOptions, args ...any) (string, error) {
	if d.DB == nil {
		return "", fmt.Errorf("db is nil")
	}

	// 获取现有表结构
	existingFieldsList, err := d.GetFields(databaseName, table)
	existingFields := ConvertToMap(existingFieldsList)
	if err != nil {
		return "", fmt.Errorf("failed to get table structure: %v", err)
	}

	alterStatements := []string{}

	// 1. 处理表名变更
	if newTableName != "" && newTableName != table {
		alterStatements = append(alterStatements, fmt.Sprintf("RENAME TABLE %v TO %v", table, newTableName))
	}

	// 2. 处理表注释变更

	var engine = "InnoDB"
	var charset = "utf8mb4"
	var collate = "utf8mb4_general_ci"
	var comment = ""
	// 方言附加参数配置，mysql可设置引擎和字符集
	if len(args) > 0 && args[0] != nil {
		arg, ok := args[0].(map[string]any)
		if ok && arg != nil {
			// 断言需要第二个参数，否则断言失败时将会panic
			charset1, _ := arg["charset"].(string)
			collate1, _ := arg["collate"].(string)
			engine1, _ := arg["engine"].(string)
			comment1, _ := arg["comment"].(string)

			log.Printf("mysql dialect addon args: charset:%v collate:%v engine:%v comment:%v", charset1, collate1, engine1, comment1)

			if engine1 != "" {
				engine = engine1
			}
			if collate1 != "" {
				collate = collate1
			}
			if charset1 != "" {
				charset = charset1
			}
			if comment1 != "" {
				comment = comment1
			}
		} else {
			log.Println("mysql dialect args assert error")
		}
	}

	// 获取现在表属性
	attrs, err := d.getTableAttributes(databaseName, table)
	if err != nil {
		return "", fmt.Errorf("failed to get table attributes: %v", err)
	}

	curEngine := attrs["engine"]
	curCharset := attrs["charset"]
	curCollate := attrs["collate"]
	curComment := attrs["comment"]

	// 对比现有表属性
	if comment != "" && comment != curComment {
		alterStatements = append(alterStatements, fmt.Sprintf("COMMENT = '%v'", comment))
	}
	if engine != "" && engine != curEngine {
		alterStatements = append(alterStatements, fmt.Sprintf("ENGINE = '%v'", engine))
	}
	if charset != "" && charset != curCharset {
		alterStatements = append(alterStatements, fmt.Sprintf("CHARSET = '%v'", charset))
	}
	if collate != "" && collate != curCollate {
		alterStatements = append(alterStatements, fmt.Sprintf("COLLATE = '%v'", collate))
	}

	// 3. 处理字段的增删改
	fieldOrderChanged := false
	previousField := "" // 用于追踪新字段列表的前序字段

	// 用于快速查找原始字段顺序
	originalFieldIndex := make(map[string]int)
	for i, field := range existingFieldsList {
		originalFieldIndex[field.Name] = i
	}

	for _, newField := range fields {
		existingField, found := existingFields[newField.Name]
		if !found {
			// 字段不存在，新增字段
			if previousField == "" {
				alterStatements = append(alterStatements, generateAddColumnSQL(newField))
			} else {
				alterStatements = append(alterStatements, generateAddColumnWithAfterSQL(newField, previousField))
			}
		} else {
			// 检查字段定义是否需要修改
			updateSQL := generateModifyColumnSQL(newField, existingField)
			if updateSQL != "" {
				alterStatements = append(alterStatements, updateSQL)
			}

			// 检查前序字段是否发生变化
			if previousField != "" {
				previousIndex := originalFieldIndex[previousField]
				currentIndex := originalFieldIndex[newField.Name]
				if currentIndex != previousIndex+1 { // 原始顺序发生变化
					fieldOrderChanged = true
					alterStatements = append(alterStatements, fmt.Sprintf("MODIFY COLUMN %v %v AFTER %v",
						newField.Name, newField.Type, previousField))
				}
			}
		}

		// 更新前序字段
		previousField = newField.Name
	}

	if fieldOrderChanged {
		fmt.Println("Field order was changed, ALTER statements generated accordingly.")
	}

	// 删除多余的字段
	for existingFieldName := range existingFields {
		found := false
		for _, newField := range fields {
			if newField.Name == existingFieldName {
				found = true
				break
			}
		}
		if !found {
			alterStatements = append(alterStatements, fmt.Sprintf("DROP COLUMN %v", existingFieldName))
		}
	}

	// 组合最终的 ALTER 语句

	if len(alterStatements) > 0 {
		alterSQL := fmt.Sprintf("ALTER TABLE `%v` \r\n", table)
		alterSQL += strings.Join(alterStatements, ",\r\n") + ";"
		return alterSQL, nil
	}

	return "", fmt.Errorf("no changes needed for table %v", table)
}

// 生成添加新字段的SQL
func generateAddColumnSQL(field interfaces.FieldOptions) string {
	sql := fmt.Sprintf("ADD COLUMN `%v` %v", field.Name, field.Type)
	if !field.IsAutoIncrement {
		if field.IsNull {
			sql += " NULL"
		} else {
			sql += " NOT NULL"
		}
	}

	if field.IsAutoIncrement {
		sql += " AUTO_INCREMENT"
	}
	if field.IsPrimary {
		sql += " PRIMARY KEY"
	}
	if field.Default != "" {
		sql += fmt.Sprintf(" DEFAULT '%v'", field.Default)
	}
	if field.Comment != "" {
		sql += fmt.Sprintf(" COMMENT '%v'", field.Comment)
	}

	return sql
}

// 生成添加新字段并指定顺序的 SQL
func generateAddColumnWithAfterSQL(field interfaces.FieldOptions, afterField string) string {
	sql := generateAddColumnSQL(field)
	sql += fmt.Sprintf(" AFTER %v", afterField)
	return sql
}

// 生成修改字段的SQL
func generateModifyColumnSQL(newField, existingField interfaces.FieldOptions) string {
	// 检查字段的变化，例如类型、是否为空、默认值等
	if newField.Type != existingField.Type || newField.IsNull != existingField.IsNull ||
		newField.IsAutoIncrement != existingField.IsAutoIncrement ||
		newField.Comment != existingField.Comment {
		sql := fmt.Sprintf("MODIFY COLUMN  `%v` %v", newField.Name, newField.Type)
		if !newField.IsAutoIncrement {
			if newField.IsNull {
				sql += " NULL"
			} else {
				sql += " NOT NULL"
			}
		}

		if newField.IsAutoIncrement {
			sql += " AUTO_INCREMENT"
		}
		if newField.Default != "" {
			sql += fmt.Sprintf(" DEFAULT '%v'", newField.Default)
		}
		if newField.Comment != "" {
			sql += fmt.Sprintf(" COMMENT '%v'", newField.Comment)
		}

		return sql
	}

	// 如果没有变化，则返回空字符串
	return ""
}

// 获取表的信息以及字段信息
func (d *MySQL) GetTableInfo(databaseName, table string) (map[string]any, []interfaces.FieldOptions, error) {
	// 获取现在表属性
	attrs, err := d.getTableAttributes(databaseName, table)
	if err != nil {
		return nil, nil, fmt.Errorf("failed to get table attributes: %v", err)
	}

	fields, err := d.GetFields(databaseName, table)
	if err != nil {
		return nil, nil, fmt.Errorf("failed to get table fields: %v", err)
	}

	return attrs, fields, nil
}

func (d *MySQL) UpdateTable(table string, fields []interfaces.FieldOptions, args ...any) error {
	if d.DB == nil {
		return fmt.Errorf("db is nil")
	}

	sql := fmt.Sprintf("ALTER TABLE %v (\r\n", table)

	for index, v := range fields {
		sql += fmt.Sprintf("CHANGE COLUMN %v %v %v", v.Name, v.Type, v.Default)

		if !v.IsAutoIncrement {
			if v.IsNull {
				sql += " NULL"
			} else {
				sql += " NOT NULL"
			}
		}

		if v.IsAutoIncrement {
			sql += " AUTO_INCREMENT"
		}
		if v.IsPrimary {
			sql += " PRIMARY KEY"
		}
		if v.Default != "" {
			sql += fmt.Sprintf(" DEFAULT '%v'", v.Default)
		}

		if v.Comment != "" {
			sql += fmt.Sprintf(" COMMENT '%v'", v.Comment)
		}

		// 字段间隔符
		if index < len(fields)-1 {
			sql += ",\r\n"
		}

	}

	var engine = "InnoDB"
	var charset = "utf8mb4"
	var collate = "utf8mb4_general_ci"
	var comment = ""
	// 方言附加参数配置，mysql可设置引擎和字符集
	if len(args) > 0 && args[0] != nil {

		arg, ok := args[0].(map[string]any)
		if ok && arg != nil {
			// 断言需要第二个参数，否则断言失败时将会panic
			charset1, _ := arg["charset"].(string)
			collate1, _ := arg["collate"].(string)
			engine1, _ := arg["engine"].(string)
			comment1, _ := arg["comment"].(string)

			log.Printf("mysql dialect addon args: charset:%v collate:%v engine:%v comment:%v", charset1, collate1, engine1, comment1)

			if engine1 != "" {
				engine = engine1
			}
			if collate1 != "" {
				collate = collate1
			}
			if charset1 != "" {
				charset = charset1
			}
			if comment1 != "" {
				comment = comment1
			}

		} else {
			log.Println("mysql dialect args assert error")
		}

	}

	if comment != "" {
		sql += fmt.Sprintf("\r\n) ENGINE=%v DEFAULT CHARSET=%v COLLATE=%v COMMENT='%v';", engine, charset, collate, comment)
	} else {
		sql += fmt.Sprintf("\r\n) ENGINE=%v DEFAULT CHARSET=%v COLLATE=%v;", engine, charset, collate)
	}

	log.Println(sql)

	return d.DB.Exec(sql).Error
}

// rename one table.
func (d *MySQL) RenameTable(oldTable string, newTable string) error {
	if d.DB == nil {
		return errors.New("db is nil")
	}
	err := d.DB.Migrator().RenameTable(oldTable, newTable)
	// err := d.DB.Exec(fmt.Sprintf("ALTER TABLE %v RENAME TO %v;", oldTable, newTable)).Error
	return err
}

func (d *MySQL) GetTables() ([]interfaces.TableDetail, error) {
	// return d.DB.Migrator().GetTables()
	const sql = `SHOW TABLE STATUS`
	var tables []interfaces.TableDetail
	var list []map[string]any
	err := d.DB.Raw(sql).Scan(&list).Error
	if err != nil {
		return nil, err
	}
	for _, v := range list {
		o := interfaces.TableDetail{}
		o.Name = v["Name"].(string)
		o.Comment = v["Comment"].(string)
		jsonData, err := json.Marshal(v)
		if err != nil {
			o.Others = ""
		} else {
			o.Others = string(jsonData)
		}
		tables = append(tables, o)
	}
	return tables, err
}

func (d *MySQL) DropTable(table string) error {
	table = strings.ToLower(table)
	if table == "mysql" || table == "information_schema" || table == "performance_schema" || table == "sys" {
		return errors.New("can not drop system table")
	}
	sql := fmt.Sprintf("DROP TABLE IF EXISTS %v;", table)
	return d.DB.Exec(sql).Error
}

func (d *MySQL) AddField(table string, field interfaces.FieldOptions, opts map[string]string) error {
	if d.DB == nil {
		return errors.New("db is nil")
	}

	// err := d.DB.Migrator().AddColumn(table, field.Name)
	var sql = "ALTER TABLE " + table + "\r\n"
	sql += " ADD COLUMN `" + field.Name + "` " + field.Type
	if field.IsPrimary {
		sql += ` PRIMARY KEY `
	}

	if field.IsAutoIncrement {
		sql += ` AUTO_INCREMENT `
	}

	if field.IsNull {
		sql += ` NULL `
	} else {
		sql += ` NOT NULL `
	}
	if field.Default != "" {
		sql += ` DEFAULT '` + field.Default + "' "
	}

	if field.Comment != "" {
		sql += ` COMMENT '` + field.Comment + "' "
	}

	if opts["afterField"] != "" {
		sql += " AFTER `" + opts["afterField"] + "`"
	}
	err := d.DB.Exec(sql).Error
	return err
}

func (d *MySQL) UpdateField(table string, oldField string, field interfaces.FieldOptions) error {
	if d.DB == nil {
		return errors.New("db is nil")
	}

	var sql = `ALTER TABLE ` + table + "\r\n"
	sql += " CHANGE COLUMN `" + oldField + "` `" + field.Name + "` " + field.Type

	if field.IsPrimary {
		sql += ` PRIMARY KEY `
	}

	if field.IsAutoIncrement {
		sql += ` AUTO_INCREMENT `
	}

	if field.IsNull {
		sql += ` NULL `
	} else {
		sql += ` NOT NULL `
	}
	if field.Default != "" {
		sql += ` DEFAULT '` + field.Default + "' "
	}

	if field.Comment != "" {
		sql += ` COMMENT '` + field.Comment + "' "
	}

	err := d.DB.Exec(sql).Error
	return err

}

func (d *MySQL) DeleteField(table string, field string) error {
	if d.DB == nil {
		return errors.New("db is nil")
	}
	err := d.DB.Migrator().DropColumn(table, field)
	return err
}

func (d *MySQL) GetFields(databaseName string, table string) ([]interfaces.FieldOptions, error) {
	var sql = `
	SELECT   
		COLUMN_NAME AS 'name' ,  
		COLUMN_TYPE AS 'type' ,  
		DATA_TYPE AS 'dataType',
		CAST(CHARACTER_MAXIMUM_LENGTH AS CHAR) AS 'length',  
		COLUMN_KEY AS 'isPrimary',  
		IS_NULLABLE AS 'isNull',  
		CASE
			WHEN EXTRA <> 'auto_increment' THEN CONCAT(COLUMN_DEFAULT, ' ', EXTRA)
			ELSE COLUMN_DEFAULT
		END AS 'default',  
		COLUMN_COMMENT AS 'comment' ,
		CASE   
        WHEN   EXTRA = 'auto_increment' THEN '是'  
        ELSE '否'  
    	END AS 'isAutoIncrement',
		CASE
			WHEN COLUMN_TYPE LIKE '%%unsigned%%' THEN '是'
		ELSE '否'
  		END AS 'isUnsigned'  
	FROM   
		INFORMATION_SCHEMA.COLUMNS  
	WHERE   
		TABLE_SCHEMA = '%v'   
		AND TABLE_NAME = '%v'
	order by ordinal_position ASC
		;
	`

	var fields = []interfaces.FieldOptions{}
	var list []map[string]any

	sql = fmt.Sprintf(sql, databaseName, table)

	if err := d.DB.Raw(sql).Scan(&list).Error; err != nil {
		return nil, err
	}
	for _, v := range list {
		var field interfaces.FieldOptions

		field.Name, _ = v["name"].(string)
		field.Type, _ = v["type"].(string)
		field.Default, _ = v["default"].(string)
		field.Comment, _ = v["comment"].(string)
		field.DataType, _ = v["dataType"].(string)
		field.Length, _ = v["length"].(string)

		if v["isUnsigned"] == "是" {
			field.IsUnsigned = true
		}

		if v["isNull"] == "YES" {
			field.IsNull = true
		}
		if v["isPrimary"] == "PRI" {
			field.IsPrimary = true
		}
		if v["isAutoIncrement"] == "是" {
			field.IsAutoIncrement = true
		}
		// fields[index] = field
		fields = append(fields, field)
	}
	return fields, nil
}

// 获取表属性
//
//	map[string]any {
//		"engine": "InnoDB",
//		"charset": "utf8mb4",
//		"collate": "utf8mb4_general_ci",
//		"comment": "comment",
//	}
func (d *MySQL) getTableAttributes(databaseName, table string) (map[string]any, error) {
	query := fmt.Sprintf(`SELECT 
		TABLE_NAME as 'name',
		ENGINE AS 'engine', 
		TABLE_COLLATION AS 'collate', 
		TABLE_COMMENT AS 'comment',
		SUBSTRING_INDEX(TABLE_COLLATION, '_', 1) AS 'charset'
	FROM information_schema.TABLES 
	WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'`, databaseName, table)
	result := map[string]any{}
	err := d.DB.Raw(query).Scan(&result).Error
	if err != nil {
		log.Println("查询出错:", err)
		return nil, err
	}

	return result, nil
}

func (d *MySQL) ExecRawSQL(sql string) ([]string, any, int64, error) {
	// var a []map[string]any
	// tx := d.DB.Exec(sql)
	// tx.Scan(&a)
	// fmt.Println(a)
	// return a, tx.RowsAffected, tx.Error

	var a []map[string]any
	var cols []string
	tx := d.DB.Raw(sql)
	rows, err := tx.Rows()
	defer func() {
		tx.AddError(rows.Close())
	}()
	if nil != err {
		tx.AddError(err)
		return cols, a, 0, err
	}
	cols, err = rows.Columns()
	if nil != err {
		tx.AddError(err)
		return cols, a, 0, err
	}

	if rows.Next() {
		tx.ScanRows(rows, &a)
	} else {
		tx.RowsAffected = 0
		tx.AddError(rows.Err())
	}
	fmt.Println(a)
	// fmt.Println(tx.Statement.RowsAffected)
	fmt.Println(tx.RowsAffected)

	return cols, a, tx.RowsAffected, tx.Error
}
